---------------------------------------------------------- Skills ---------------------------------------

---------- Skills_GetAll
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Skills_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Skills_GetAll]
GO

CREATE PROCEDURE [dbo].[Skills_GetAll]
AS
	SELECT
		[dbo].[Skills].*
	FROM
		[dbo].[Skills]
	ORDER BY
		[NAME] ASC
GO
-- EXEC [dbo].[Skills_GetAll]


---------- Skills_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Skills_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Skills_GetByID]
GO

CREATE PROCEDURE [dbo].[Skills_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[Skills]
	WHERE
		[dbo].[Skills].[ID] = @id
GO
-- EXEC [dbo].[Skills_GetByID] 1


---------- Skills_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Skills_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Skills_Create]
GO

CREATE PROCEDURE [dbo].[Skills_Create]
	@name varchar(100),
	@id integer OUTPUT
AS
	INSERT INTO	[dbo].[Skills]
	(
		[NAME]
	)
	VALUES
	(
		RTRIM(LTRIM(@name))
	)
	
	SET @id = SCOPE_IDENTITY();
GO
/*
DECLARE @id integer
EXEC [dbo].[Skills_Create] 'Sales', @id=@id OUTPUT
SELECT @id
*/


---------- Skills_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Skills_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Skills_Update]
GO

CREATE PROCEDURE [dbo].[Skills_Update]
	@id integer,
	@name varchar(100)
AS
	UPDATE
		[dbo].[Skills]
	SET
		[NAME] = RTRIM(LTRIM(@name))
	WHERE
		[dbo].[Skills].[ID] = @id

	
GO
-- EXEC [dbo].[Skills_Update] 1, 'Sales'


---------- Skills_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Skills_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Skills_Delete]
GO

CREATE PROCEDURE [dbo].[Skills_Delete]
	@id integer
AS
	DELETE
	FROM
		[dbo].[Skills]
	WHERE
		[dbo].[Skills].[ID] = @id
GO
-- EXEC [dbo].[Skills_Delete] 11

------------------------------------------------------- END - Skills ------------------------------------



------------------------------------------------------- OfficeEndPoints ---------------------------------

---------- OfficeEndPoints_GetAll
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OfficeEndPoints_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[OfficeEndPoints_GetAll]
GO

CREATE PROCEDURE [dbo].[OfficeEndPoints_GetAll]
AS
	SELECT
		*
	FROM
		[dbo].[OfficeEndPoints]
	ORDER BY
		[SIP] ASC
GO
-- EXEC [dbo].[OfficeEndPoints_GetAll]


---------- OfficeEndPoints_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OfficeEndPoints_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[OfficeEndPoints_GetByID]
GO

CREATE PROCEDURE [dbo].[OfficeEndPoints_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[OfficeEndPoints]
	WHERE
		[dbo].[OfficeEndPoints].[ID] = @id
GO
-- EXEC [dbo].[OfficeEndPoints_GetByID] 1


---------- OfficeEndPoints_GetBySip
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OfficeEndPoints_GetBySip]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[OfficeEndPoints_GetBySip]
GO

CREATE PROCEDURE [dbo].[OfficeEndPoints_GetBySip]
	@sip varchar(200)
AS
	SELECT
		*
	FROM
		[dbo].[OfficeEndPoints]
	WHERE
		LOWER([dbo].[OfficeEndPoints].[SIP]) = LOWER(LTRIM(RTRIM(@sip)))
GO
-- EXEC [dbo].[OfficeEndPoints_GetByTel] 'sip:bla'


---------- OfficeEndPoints_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OfficeEndPoints_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[OfficeEndPoints_Create]
GO

CREATE PROCEDURE [dbo].[OfficeEndPoints_Create]
	@sip varchar(200),
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[OfficeEndPoints]
	(
		[SIP]
	)
	VALUES
	(
		LOWER(RTRIM(LTRIM(@sip)))
	)
	
	SET @id = SCOPE_IDENTITY();
GO
/*
DECLARE @id integer
EXEC [dbo].[OfficeEndPoints_Create] 'cluj.espressoft.ro', 'sip:ofice@cluj.espressoft.ro', @id=@id OUTPUT
SELECT @id
*/



---------- OfficeEndPoints_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OfficeEndPoints_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[OfficeEndPoints_Update]
GO

CREATE PROCEDURE [dbo].[OfficeEndPoints_Update]
	@id integer,
	@sip varchar(200)
AS
	UPDATE
		[dbo].[OfficeEndPoints]
	SET
		[SIP] = LOWER(RTRIM(LTRIM(@sip)))
	WHERE
		[dbo].[OfficeEndPoints].[ID] = @id
GO
-- EXEC [dbo].[OfficeEndPoints_Update] 2, 'cluj.espressoft.ro', 'sip:bla'


---------- OfficeEndPoints_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OfficeEndPoints_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[OfficeEndPoints_Delete]
GO

CREATE PROCEDURE [dbo].[OfficeEndPoints_Delete]
	@id integer
AS
	DELETE
	FROM
		[dbo].[OfficeEndPoints]
	WHERE
		[dbo].[OfficeEndPoints].[ID] = @id
GO
-- EXEC [dbo].[OfficeEndPoints_Delete] 1

------------------------------------------------- END - OfficeEndPoints -----------------------------



----------------------------------------------------- Supervisors -----------------------------------

---------- Supervisors_GetAll
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Supervisors_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Supervisors_GetAll]
GO

CREATE PROCEDURE [dbo].[Supervisors_GetAll]
AS
	SELECT
		[dbo].[Supervisors].*
	FROM
		[dbo].[Supervisors]
	ORDER BY
		[DOMAIN_LOGON_NAME] ASC, [OFFICE_ENDPOINT_ID] ASC
GO
-- EXEC [dbo].[Supervisors_GetAll]


---------- Supervisors_GetByOfficeEndPointID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Supervisors_GetByOfficeEndPointID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Supervisors_GetByOfficeEndPointID]
GO

CREATE PROCEDURE [dbo].[Supervisors_GetByOfficeEndPointID]
	@office_endpoint_id integer
AS
	SELECT
		[dbo].[Supervisors].*
	FROM
		[dbo].[Supervisors]
	WHERE
		[OFFICE_ENDPOINT_ID] = @office_endpoint_id
	ORDER BY
		[DOMAIN_LOGON_NAME] ASC, [OFFICE_ENDPOINT_ID] ASC
GO
-- EXEC [dbo].[Supervisors_GetByOfficeEndPointID] 1


---------- Supervisors_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Supervisors_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Supervisors_GetByID]
GO

CREATE PROCEDURE [dbo].[Supervisors_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[Supervisors]
	WHERE
		[dbo].[Supervisors].[ID] = @id
GO
-- EXEC [dbo].[Supervisors_GetByID] 1


---------- Supervisors_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Supervisors_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Supervisors_Create]
GO

CREATE PROCEDURE [dbo].[Supervisors_Create]
	@office_endpoint_id integer,
	@domain_logon_name varchar(100),
	@domain_oid uniqueidentifier,
	@id integer OUTPUT
AS
	INSERT INTO	[dbo].[Supervisors]
	(
		[OFFICE_ENDPOINT_ID],
		[DOMAIN_LOGON_NAME],
		[DOMAIN_OID]
	)
	VALUES
	(
		@office_endpoint_id,
		LOWER(RTRIM(LTRIM(@domain_logon_name))),
		@domain_oid
	)
	
	SET @id = SCOPE_IDENTITY();
GO
/*
DECLARE @id integer
EXEC [dbo].[Supervisors_Create] 1, 'flavius', @id=@id OUTPUT
SELECT @id
*/


---------- Supervisors_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Supervisors_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Supervisors_Update]
GO

CREATE PROCEDURE [dbo].[Supervisors_Update]
	@id integer,
	@domain_logon_name varchar(100)
AS
	UPDATE
		[dbo].[Supervisors]
	SET
		[DOMAIN_LOGON_NAME] = LOWER(RTRIM(LTRIM(@domain_logon_name)))
	WHERE
		[dbo].[Supervisors].[ID] = @id
GO
-- EXEC [dbo].[Supervisors_Update] 1, 'flavius'


---------- Supervisors_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Supervisors_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Supervisors_Delete]
GO

CREATE PROCEDURE [dbo].[Supervisors_Delete]
	@id integer
AS
	DELETE
	FROM
		[dbo].[Supervisors]
	WHERE
		[dbo].[Supervisors].[ID] = @id
GO
-- EXEC [dbo].[Supervisors_Delete] 1

--------------------------------------------------- END - Supervisors ------------------------------------



-------------------------------------------------------- Agents ------------------------------------------

---------- Agents_GetAll
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agents_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Agents_GetAll]
GO

CREATE PROCEDURE [dbo].[Agents_GetAll]
AS
	SELECT
		[dbo].[Agents].*
	FROM
		[dbo].[Agents]
	ORDER BY
		[DOMAIN_LOGON_NAME] ASC, [OFFICE_ENDPOINT_ID] ASC
GO
-- EXEC [dbo].[Agents_GetAll]


---------- Agents_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agents_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Agents_GetByID]
GO

CREATE PROCEDURE [dbo].[Agents_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[Agents]
	WHERE
		[dbo].[Agents].[ID] = @id
GO
-- EXEC [dbo].[Agents_GetByID] 1


---------- Agents_GetBySkillIDAndEndpointID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agents_GetBySkillIDAndEndpointID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].Agents_GetBySkillIDAndEndpointID
GO

CREATE PROCEDURE [dbo].Agents_GetBySkillIDAndEndpointID
	@SkillID integer,
	@EndpointID integer
AS
	SELECT
		[dbo].[Agents].*
	FROM
		[dbo].[AgentSkills]
		INNER JOIN [dbo].[Agents] ON [dbo].[Agents].ID = [dbo].[AgentSkills].AGENT_ID
	WHERE
		[dbo].AgentSkills.SKILL_ID = @SkillID
		AND [dbo].[Agents].OFFICE_ENDPOINT_ID = @EndpointID
GO
  -- EXEC [dbo].[Agents_GetBySkillIDAndEndpointID] 1, 1


---------- Agents_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agents_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Agents_Create]
GO

CREATE PROCEDURE [dbo].[Agents_Create]
	@office_endpoint_id integer,
	@domain_logon_name varchar(100),
	@domain_oid uniqueidentifier,
	@id integer OUTPUT
AS
	INSERT INTO	[dbo].[Agents]
	(
		[OFFICE_ENDPOINT_ID],
		[DOMAIN_LOGON_NAME],
		[DOMAIN_OID]
	)
	VALUES
	(
		@office_endpoint_id,
		LOWER(RTRIM(LTRIM(@domain_logon_name))),
		@domain_oid
	)
	
	SET @id = SCOPE_IDENTITY();
GO
/*
DECLARE @id integer
EXEC [dbo].[Agents_Create] 1, 'carmeng', @id=@id OUTPUT
SELECT @id
*/


---------- Agents_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agents_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Agents_Update]
GO

CREATE PROCEDURE [dbo].[Agents_Update]
	@id integer,
	@domain_logon_name varchar(100)
AS
	UPDATE
		[dbo].[Agents]
	SET
		[DOMAIN_LOGON_NAME] = LOWER(RTRIM(LTRIM(@domain_logon_name)))
	WHERE
		[dbo].[Agents].[ID] = @id
GO
-- EXEC [dbo].[Agents_Update] 1, 'flavius'


---------- Agents_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agents_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Agents_Delete]
GO

CREATE PROCEDURE [dbo].[Agents_Delete]
	@id integer
AS
	DELETE
	FROM
		[dbo].[Agents]
	WHERE
		[dbo].[Agents].[ID] = @id
GO
-- EXEC [dbo].[AgentSkills_DeleteByAgentID] 2
-- EXEC [dbo].[Agents_Delete] 2



---------- Agents_GetByOfficeEndPointID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Agents_GetByOfficeEndPointID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[Agents_GetByOfficeEndPointID]
GO

CREATE PROCEDURE [dbo].[Agents_GetByOfficeEndPointID]
	@office_endpoint_id integer
AS
	SELECT
		*
	FROM
		[dbo].[Agents]
	WHERE
		[OFFICE_ENDPOINT_ID] = @office_endpoint_id
	ORDER BY
		[DOMAIN_LOGON_NAME] ASC, [OFFICE_ENDPOINT_ID] ASC
GO
-- EXEC [dbo].[Agents_GetByOfficeEndPointID] 1

----------------------------------------------------- END - Agents ---------------------------------------



------------------------------------------------------ AgentSkills ---------------------------------------

---------- AgentSkills_GetByAgentID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentSkills_GetByAgentID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentSkills_GetByAgentID]
GO

CREATE PROCEDURE [dbo].[AgentSkills_GetByAgentID]
	@agent_id integer
AS
	SELECT
		*
	FROM
		[dbo].[AgentSkills]
	WHERE
		[AGENT_ID] = @agent_id
GO
-- EXEC [dbo].[AgentSkills_GetByAgentID] 1


---------- AgentSkills_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentSkills_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentSkills_Create]
GO

CREATE PROCEDURE [dbo].[AgentSkills_Create]
	@agent_id integer,
	@skill_id integer,
	@skill_value integer
AS
	INSERT INTO [dbo].[AgentSkills]
	(
		[AGENT_ID],
		[SKILL_ID],
		[SKILL_VALUE]
	)
	VALUES
	(
		@agent_id,
		@skill_id,
		@skill_value
	)

	-- update changed date on office
	UPDATE
		[dbo].[OfficeEndPoints]
	SET
		[dbo].[OfficeEndPoints].[CHANGED_ON] = GETDATE()
	WHERE
		[dbo].[OfficeEndPoints].[ID] in 
			(
				select [OFFICE_ENDPOINT_ID] from [dbo].[Agents] where [dbo].[Agents].[ID] = @agent_id
			)
GO
/*
EXEC [dbo].[AgentSkills_Create] 1, 1, 5
*/


---------- AgentSkills_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentSkills_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentSkills_Delete]
GO

CREATE PROCEDURE [dbo].[AgentSkills_Delete]
	@agent_id integer,
	@skill_id integer
AS
	DELETE FROM
		[dbo].[AgentSkills]
	WHERE
		[AGENT_ID] = @agent_id AND [SKILL_ID] = @skill_id

	-- update changed date on office
	UPDATE
		[dbo].[OfficeEndPoints]
	SET
		[dbo].[OfficeEndPoints].[CHANGED_ON] = GETDATE()
	WHERE
		[dbo].[OfficeEndPoints].[ID] in 
			(
				select [OFFICE_ENDPOINT_ID] from [dbo].[Agents] where [dbo].[Agents].[ID] = @agent_id
			)
GO
/*
EXEC [dbo].[AgentSkills_Delete] 1, 1
*/


---------- AgentSkills_DeleteByAgentID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentSkills_DeleteByAgentID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentSkills_DeleteByAgentID]
GO

CREATE PROCEDURE [dbo].[AgentSkills_DeleteByAgentID]
	@agent_id integer
AS
	DELETE FROM
		[dbo].[AgentSkills]
	WHERE
		[AGENT_ID] = @agent_id

	-- update changed date on office
	UPDATE
		[dbo].[OfficeEndPoints]
	SET
		[dbo].[OfficeEndPoints].[CHANGED_ON] = GETDATE()
	WHERE
		[dbo].[OfficeEndPoints].[ID] in 
			(
				select [OFFICE_ENDPOINT_ID] from [dbo].[Agents] where [dbo].[Agents].[ID] = @agent_id
			)
GO
/*
EXEC [dbo].[AgentSkills_DeleteByAgentID] 1
*/

---------------------------------------------------- END - AgentSkills -----------------------------------



---------------------------------------------------- CustomerEndPoints -----------------------------------

---------- CustomerEndPoints_GetAll
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerEndPoints_GetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[CustomerEndPoints_GetAll]
GO

CREATE PROCEDURE [dbo].[CustomerEndPoints_GetAll]
AS
	SELECT
		*
	FROM
		[dbo].[CustomerEndPoints]
	ORDER BY
		[TEL] ASC
GO
-- EXEC [dbo].[CustomerEndPoints_GetAll]

---------- CustomerEndPoints_GetAllDetails
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerEndPoints_GetAllDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].CustomerEndPoints_GetAllDetails
GO

CREATE PROCEDURE [dbo].[CustomerEndPoints_GetAllDetails]
AS
	SELECT
		[dbo].CustomerEndPoints.*,
		[dbo].Skills.NAME AS SkillName,
		[dbo].OfficeEndPoints.SIP AS OfficeSIP

	FROM
		[dbo].[CustomerEndPoints]
	INNER JOIN [dbo].[Skills] ON [dbo].CustomerEndPoints.SKILL_ID = [dbo].Skills.ID
	INNER JOIN [dbo].OfficeEndPoints ON [dbo].OfficeEndPoints.ID = [dbo].CustomerEndPoints.OFFICE_ENDPOINT_ID
	ORDER BY
		[TEL] ASC
GO
 --EXEC [dbo].[CustomerEndPoints_GetAllDetails]


---------- CustomerEndPoints_GetByID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerEndPoints_GetByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[CustomerEndPoints_GetByID]
GO

CREATE PROCEDURE [dbo].[CustomerEndPoints_GetByID]
	@id integer
AS
	SELECT
		*
	FROM
		[dbo].[CustomerEndPoints]
	WHERE
		[dbo].[CustomerEndPoints].[ID] = @id
GO
-- EXEC [dbo].[CustomerEndPoints_GetByID] 1


---------- CustomerEndPoints_GetByTel
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerEndPoints_GetByTel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[CustomerEndPoints_GetByTel]
GO

CREATE PROCEDURE [dbo].[CustomerEndPoints_GetByTel]
	@tel varchar(50)
AS
	SELECT
		*
	FROM
		[dbo].[CustomerEndPoints]
	WHERE
		LOWER([dbo].[CustomerEndPoints].[TEL]) = LTRIM(RTRIM(@tel))
GO
-- EXEC [dbo].[CustomerEndPoints_GetByTel] 'tel:bla'


---------- CustomerEndPoints_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerEndPoints_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[CustomerEndPoints_Create]
GO

CREATE PROCEDURE [dbo].[CustomerEndPoints_Create]
	@tel varchar(50),
	@skill_id integer,
	@office_endpoint_id integer,
	@id integer OUTPUT
AS
	INSERT INTO [dbo].[CustomerEndPoints]
	(
		[TEL],
		[SKILL_ID],
		[OFFICE_ENDPOINT_ID]
	)
	VALUES
	(
		RTRIM(LTRIM(@tel)),
		@skill_id,
		@office_endpoint_id
	)
	
	SET @id = SCOPE_IDENTITY();
GO
/*
DECLARE @id integer
EXEC [dbo].[CustomerEndPoints_Create] ' 0744611770', 1, 1, @id=@id OUTPUT
SELECT @id
*/



---------- CustomerEndPoints_Update
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerEndPoints_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[CustomerEndPoints_Update]
GO

CREATE PROCEDURE [dbo].[CustomerEndPoints_Update]
	@id integer,
	@tel varchar(50),
	@skill_id integer,
	@office_endpoint_id integer
AS
	UPDATE
		[dbo].[CustomerEndPoints]
	SET
		[SKILL_ID] = @skill_id,
		[TEL] = @tel,
		[OFFICE_ENDPOINT_ID] = @office_endpoint_id
	WHERE
		[dbo].[CustomerEndPoints].[ID] = @id
GO
-- EXEC [dbo].[CustomerEndPoints_Update] 2, 1


---------- CustomerEndPoints_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerEndPoints_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[CustomerEndPoints_Delete]
GO

CREATE PROCEDURE [dbo].[CustomerEndPoints_Delete]
	@id integer
AS
	DELETE
	FROM
		[dbo].[CustomerEndPoints]
	WHERE
		[dbo].[CustomerEndPoints].[ID] = @id
GO
-- EXEC [dbo].[CustomerEndPoints_Delete] 1

------------------------------------------------- END - CustomerEndPoints -----------------------------



--------------------------------------------------- AgentDistributions --------------------------------

---------- AgentDistributions_Create
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentDistributions_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentDistributions_Create]
GO

CREATE PROCEDURE [dbo].[AgentDistributions_Create]
	@agent_id integer,
	@customer_endpoint_id integer
AS
	INSERT INTO [dbo].[AgentDistributions]
	(
		[AGENT_ID],
		[CUSTOMER_ENDPOINT_ID]
	)
	VALUES
	(
		@agent_id,
		@customer_endpoint_id
	)
GO
/*
EXEC [dbo].[AgentDistributions_Create] 1, 1
*/

---------- AgentDistributions_CreateIfNotExists
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentDistributions_CreateIfNotExists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentDistributions_CreateIfNotExists]
GO

CREATE PROCEDURE [dbo].[AgentDistributions_CreateIfNotExists]
	@agent_id integer,
	@customer_endpoint_id integer
AS
	IF NOT EXISTS( SELECT * from [dbo].[AgentDistributions] WHERE [AGENT_ID] = @agent_id AND [CUSTOMER_ENDPOINT_ID] = @customer_endpoint_id)
	BEGIN
		INSERT INTO [dbo].[AgentDistributions]
		(
			[AGENT_ID],
			[CUSTOMER_ENDPOINT_ID]
		)
		VALUES
		(
			@agent_id,
			@customer_endpoint_id
		)
	END
GO
/*
EXEC [dbo].[AgentDistributions_CreateIfNotExists] 1, 1
*/

---------- AgentDistributions_Delete
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentDistributions_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentDistributions_Delete]
GO

CREATE PROCEDURE [dbo].[AgentDistributions_Delete]
	@agent_id integer,
	@customer_endpoint_id integer
AS
	DELETE FROM
		[dbo].[AgentDistributions]
	WHERE
		[AGENT_ID] = @agent_id
		AND
		[CUSTOMER_ENDPOINT_ID] = @customer_endpoint_id
GO
/*
EXEC [dbo].[AgentDistributions_Delete] 1, 1
*/


---------- AgentDistributions_DeleteByAgentID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentDistributions_DeleteByAgentID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentDistributions_DeleteByAgentID]
GO

CREATE PROCEDURE [dbo].[AgentDistributions_DeleteByAgentID]
	@agent_id integer
AS
	DELETE FROM
		[dbo].[AgentDistributions]
	WHERE
		[AGENT_ID] = @agent_id
GO
/*
EXEC [dbo].[AgentDistributions_DeleteByAgentID] 1
*/

---------- AgentDistributions_DeleteByCustomerEndpointID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentDistributions_DeleteByCustomerEndpointID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentDistributions_DeleteByCustomerEndpointID]
GO

CREATE PROCEDURE [dbo].[AgentDistributions_DeleteByCustomerEndpointID]
	@id integer
AS
	DELETE FROM
		[dbo].[AgentDistributions]
	WHERE
		[dbo].AgentDistributions.CUSTOMER_ENDPOINT_ID = @id
GO
/*
EXEC [dbo].[AgentDistributions_DeleteByCustomerEndpointID] 1
*/

---------- AgentDistributions_GetByCustomerEndPointID
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentDistributions_GetByCustomerEndPointID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[AgentDistributions_GetByCustomerEndPointID]
GO

CREATE PROCEDURE [dbo].[AgentDistributions_GetByCustomerEndPointID]
	@customer_endpoint_id integer
AS
	SELECT
		[dbo].[AgentDistributions].[AGENT_ID],
		[dbo].[AgentDistributions].[CUSTOMER_ENDPOINT_ID]
	FROM
		[dbo].[AgentDistributions]
	INNER JOIN
		[dbo].[Agents] on [dbo].[AgentDistributions].[AGENT_ID] = [dbo].[Agents].[ID]
	WHERE
		[CUSTOMER_ENDPOINT_ID] = @customer_endpoint_id
	ORDER BY
		[dbo].[Agents].[DOMAIN_LOGON_NAME] ASC
GO
/*
EXEC [dbo].[AgentDistributions_GetByCustomerEndPointID] 1
*/

------------------------------------------------- END - AgentDistributions -----------------------------------
